#!/bin/bash

# dwd_to_dws_1d_init.sh all/表名 日期
# 1、判断参数是否传入
if [ $# -ne 2 ]; then
    echo "参数错误，请传入表名（all 代表所有表）和日期，格式：dwd_to_dws_1d_init.sh all/表名 日期"
    exit 1
fi

table_name=$1
date=$2

# 定义函数来执行首日加载的 SQL
execute_initial_load() {
    local sql=$1
    /opt/module/hive/bin/hive -e "use medical;set hive.exec.dynamic.partition.mode=nonstrict;$sql"
    if [ $? -ne 0 ]; then
        echo "执行 SQL 失败: $sql"
        exit 1
    fi
}

# 根据表名匹配首日加载
case $table_name in
    "all")
        # 交易域医院患者性别年龄段粒度问诊最近1日汇总表
        sql="insert overwrite table dws_trade_hospital_gender_age_group_consultation_1d
    partition (dt)
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(consultation_fee) consultation_amount,
       count(*)              consultation_count,
       '$date'
from (select hospital_id,
             hospital_name,
             gender_code,
             gender,
             case
                 when age >= 0 and age <= 2 then '婴儿期'
                 when age >= 3 and age <= 5 then '幼儿期'
                 when age >= 6 and age <= 11 then '小学阶段'
                 when age >= 12 and age <= 17 then '青少年期（中学阶段）'
                 when age >= 18 and age <= 29 then '青年期'
                 when age >= 30 and age <= 59 then '中年期'
                 when age >= 60 and age <= 122 then '老年期'
                 else '年龄异常' end age_group,
             consultation_fee,
             '$date'
      from (select doctor_id,
                   patient_id,
                   consultation_fee,
                   '$date'
            from dwd_trade_consultation_inc) consul
               left join
           (select id,
                   hospital_id
            from dim_doctor_full
            where dt = '$date') doc
           on doctor_id = doc.id
               left join (select id,
                                 name hospital_name
                          from dim_hospital_full
                          where dt = '$date') hos
                         on doc.hospital_id = hos.id
               left join
           (select id,
                   gender_code,
                   gender,
                   year('$date') - year(birthday) age
            from dim_patient_full
            where dt = '$date') patient
           on patient_id = patient.id) with_group
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group,
         '$date';"
        execute_initial_load "$sql"

        # 交易域医院患者性别年龄段粒度问诊支付成功最近1日汇总表
        sql="insert overwrite table dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
    partition (dt)
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(consultation_fee) consultation_pay_suc_amount,
       count(*)              consultation_pay_suc_count,
       '$date'
from (select hospital_id,
             hospital_name,
             gender_code,
             gender,
             case
                 when age >= 0 and age <= 2 then '婴儿期'
                 when age >= 3 and age <= 5 then '幼儿期'
                 when age >= 6 and age <= 11 then '小学阶段'
                 when age >= 12 and age <= 17 then '青少年期（中学阶段）'
                 when age >= 18 and age <= 29 then '青年期'
                 when age >= 30 and age <= 59 then '中年期'
                 when age >= 60 and age <= 122 then '老年期'
                 else '年龄异常' end age_group,
             consultation_fee,
             '$date'
      from (select doctor_id,
                   patient_id,
                   consultation_fee,
                   '$date'
            from dwd_trade_consultation_pay_suc_inc) consul
               left join
           (select id,
                   hospital_id
            from dim_doctor_full
            where dt = '$date') doc
           on doctor_id = doc.id
               left join (select id,
                                 name hospital_name
                          from dim_hospital_full
                          where dt = '$date') hos
                         on doc.hospital_id = hos.id
               left join
           (select id,
                   gender_code,
                   gender,
                   year('$date') - year(birthday) age
            from dim_patient_full
            where dt = '$date') patient
           on patient_id = patient.id) with_group
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group,
         '$date';"
        execute_initial_load "$sql"

        # 交易域医院患者性别年龄段粒度处方开单最近1日汇总表
        sql="insert overwrite table dws_trade_hospital_gender_age_group_prescription_1d
    partition (dt)
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(total_amount) prescription_amount,
       count(*)          prescription_count,
       '$date'
from (select hospital_id,
             hospital_name,
             gender_code,
             gender,
             case
                 when age >= 0 and age <= 2 then '婴儿期'
                 when age >= 3 and age <= 5 then '幼儿期'
                 when age >= 6 and age <= 11 then '小学阶段'
                 when age >= 12 and age <= 17 then '青少年期（中学阶段）'
                 when age >= 18 and age <= 29 then '青年期'
                 when age >= 30 and age <= 59 then '中年期'
                 when age >= 60 and age <= 122 then '老年期'
                 else '年龄异常' end age_group,
             total_amount,
             '$date'
      from (select max(doctor_id)    doctor_id,
                   max(patient_id)   patient_id,
                   max(total_amount) total_amount,
                   max('$date')      dt
            from dwd_trade_prescription_inc
            group by prescription_id) prescr
               left join
           (select id,
                   hospital_id
            from dim_doctor_full
            where dt = '$date') doc
           on doctor_id = doc.id
               left join (select id,
                                 name hospital_name
                          from dim_hospital_full
                          where dt = '$date') hos
                         on doc.hospital_id = hos.id
               left join
           (select id,
                   gender_code,
                   gender,
                   year('$date') - year(birthday) age
            from dim_patient_full
            where dt = '$date') patient
           on patient_id = patient.id) with_group
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group,
         '$date';"
        execute_initial_load "$sql"

        # 交易域医院患者性别年龄段粒度处方开单支付成功最近1日汇总表
        sql="insert overwrite table dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
    partition (dt)
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(total_amount) prescription_pay_suc_amount,
       count(*)          prescription_pay_suc_count,
       '$date'
from (select hospital_id,
             hospital_name,
             gender_code,
             gender,
             case
                 when age >= 0 and age <= 2 then '婴儿期'
                 when age >= 3 and age <= 5 then '幼儿期'
                 when age >= 6 and age <= 11 then '小学阶段'
                 when age >= 12 and age <= 17 then '青少年期（中学阶段）'
                 when age >= 18 and age <= 29 then '青年期'
                 when age >= 30 and age <= 59 then '中年期'
                 when age >= 60 and age <= 122 then '老年期'
                 else '年龄异常' end age_group,
             total_amount,
             '$date'
      from (select max(doctor_id)    doctor_id,
                   max(patient_id)   patient_id,
                   max(total_amount) total_amount,
                   max('$date')      dt
            from dwd_trade_prescription_pay_suc_inc
            group by prescription_id) prescr
               left join
           (select id,
                   hospital_id
            from dim_doctor_full
            where dt = '$date') doc
           on doctor_id = doc.id
               left join (select id,
                                 name hospital_name
                          from dim_hospital_full
                          where dt = '$date') hos
                         on doc.hospital_id = hos.id
               left join
           (select id,
                   gender_code,
                   gender,
                   year('$date') - year(birthday) age
            from dim_patient_full
            where dt = '$date') patient
           on patient_id = patient.id) with_group
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group,
         '$date';"
        execute_initial_load "$sql"

        # 交易域医生粒度问诊最近1日汇总表
        sql="insert overwrite table dws_trade_doctor_consultation_1d
    partition (dt)
select doctor_id,
       name doctor_name,
       consultation_count,
       '$date'
from (select doctor_id,
             '$date',
             count(*) consultation_count
      from dwd_trade_consultation_inc
      group by doctor_id,
               '$date') avg
         left join (select id,
                           name
                    from dim_doctor_full
                    where dt = '$date') doc
                   on avg.doctor_id = doc.id;"
        execute_initial_load "$sql"
        ;;
	"dws_trade_hospital_gender_age_group_consultation_1d")
	sql="insert overwrite table dws_trade_hospital_gender_age_group_consultation_1d
    partition (dt)
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(consultation_fee) consultation_amount,
       count(*)              consultation_count,
       '$date'
from (select hospital_id,
             hospital_name,
             gender_code,
             gender,
             case
                 when age >= 0 and age <= 2 then '婴儿期'
                 when age >= 3 and age <= 5 then '幼儿期'
                 when age >= 6 and age <= 11 then '小学阶段'
                 when age >= 12 and age <= 17 then '青少年期（中学阶段）'
                 when age >= 18 and age <= 29 then '青年期'
                 when age >= 30 and age <= 59 then '中年期'
                 when age >= 60 and age <= 122 then '老年期'
                 else '年龄异常' end age_group,
             consultation_fee,
             '$date'
      from (select doctor_id,
                   patient_id,
                   consultation_fee,
                   '$date'
            from dwd_trade_consultation_inc) consul
               left join
           (select id,
                   hospital_id
            from dim_doctor_full
            where dt = '$date') doc
           on doctor_id = doc.id
               left join (select id,
                                 name hospital_name
                          from dim_hospital_full
                          where dt = '$date') hos
                         on doc.hospital_id = hos.id
               left join
           (select id,
                   gender_code,
                   gender,
                   year('$date') - year(birthday) age
            from dim_patient_full
            where dt = '$date') patient
           on patient_id = patient.id) with_group
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group,
         '$date';"
        execute_initial_load "$sql"
		;;
	"dws_trade_hospital_gender_age_group_consultation_pay_suc_1d")
	sql="insert overwrite table dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
    partition (dt)
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(consultation_fee) consultation_pay_suc_amount,
       count(*)              consultation_pay_suc_count,
       '$date'
from (select hospital_id,
             hospital_name,
             gender_code,
             gender,
             case
                 when age >= 0 and age <= 2 then '婴儿期'
                 when age >= 3 and age <= 5 then '幼儿期'
                 when age >= 6 and age <= 11 then '小学阶段'
                 when age >= 12 and age <= 17 then '青少年期（中学阶段）'
                 when age >= 18 and age <= 29 then '青年期'
                 when age >= 30 and age <= 59 then '中年期'
                 when age >= 60 and age <= 122 then '老年期'
                 else '年龄异常' end age_group,
             consultation_fee,
             '$date'
      from (select doctor_id,
                   patient_id,
                   consultation_fee,
                   '$date'
            from dwd_trade_consultation_pay_suc_inc) consul
               left join
           (select id,
                   hospital_id
            from dim_doctor_full
            where dt = '$date') doc
           on doctor_id = doc.id
               left join (select id,
                                 name hospital_name
                          from dim_hospital_full
                          where dt = '$date') hos
                         on doc.hospital_id = hos.id
               left join
           (select id,
                   gender_code,
                   gender,
                   year('$date') - year(birthday) age
            from dim_patient_full
            where dt = '$date') patient
           on patient_id = patient.id) with_group
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group,
         '$date';"
        execute_initial_load "$sql"
		;;
	"dws_trade_hospital_gender_age_group_prescription_1d")
	sql="insert overwrite table dws_trade_hospital_gender_age_group_prescription_1d
    partition (dt)
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(total_amount) prescription_amount,
       count(*)          prescription_count,
       '$date'
from (select hospital_id,
             hospital_name,
             gender_code,
             gender,
             case
                 when age >= 0 and age <= 2 then '婴儿期'
                 when age >= 3 and age <= 5 then '幼儿期'
                 when age >= 6 and age <= 11 then '小学阶段'
                 when age >= 12 and age <= 17 then '青少年期（中学阶段）'
                 when age >= 18 and age <= 29 then '青年期'
                 when age >= 30 and age <= 59 then '中年期'
                 when age >= 60 and age <= 122 then '老年期'
                 else '年龄异常' end age_group,
             total_amount,
             '$date'
      from (select max(doctor_id)    doctor_id,
                   max(patient_id)   patient_id,
                   max(total_amount) total_amount,
                   max('$date')      dt
            from dwd_trade_prescription_inc
            group by prescription_id) prescr
               left join
           (select id,
                   hospital_id
            from dim_doctor_full
            where dt = '$date') doc
           on doctor_id = doc.id
               left join (select id,
                                 name hospital_name
                          from dim_hospital_full
                          where dt = '$date') hos
                         on doc.hospital_id = hos.id
               left join
           (select id,
                   gender_code,
                   gender,
                   year('$date') - year(birthday) age
            from dim_patient_full
            where dt = '$date') patient
           on patient_id = patient.id) with_group
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group,
         '$date';"
        execute_initial_load "$sql"
		;;
	"dws_trade_hospital_gender_age_group_prescription_pay_suc_1d")
	sql="insert overwrite table dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
    partition (dt)
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(total_amount) prescription_pay_suc_amount,
       count(*)          prescription_pay_suc_count,
       '$date'
from (select hospital_id,
             hospital_name,
             gender_code,
             gender,
             case
                 when age >= 0 and age <= 2 then '婴儿期'
                 when age >= 3 and age <= 5 then '幼儿期'
                 when age >= 6 and age <= 11 then '小学阶段'
                 when age >= 12 and age <= 17 then '青少年期（中学阶段）'
                 when age >= 18 and age <= 29 then '青年期'
                 when age >= 30 and age <= 59 then '中年期'
                 when age >= 60 and age <= 122 then '老年期'
                 else '年龄异常' end age_group,
             total_amount,
             '$date'
      from (select max(doctor_id)    doctor_id,
                   max(patient_id)   patient_id,
                   max(total_amount) total_amount,
                   max('$date')      dt
            from dwd_trade_prescription_pay_suc_inc
            group by prescription_id) prescr
               left join
           (select id,
                   hospital_id
            from dim_doctor_full
            where dt = '$date') doc
           on doctor_id = doc.id
               left join (select id,
                                 name hospital_name
                          from dim_hospital_full
                          where dt = '$date') hos
                         on doc.hospital_id = hos.id
               left join
           (select id,
                   gender_code,
                   gender,
                   year('$date') - year(birthday) age
            from dim_patient_full
            where dt = '$date') patient
           on patient_id = patient.id) with_group
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group,
         '$date';"
        execute_initial_load "$sql"
		;;
	"dws_trade_doctor_consultation_1d")
	sql="insert overwrite table dws_trade_doctor_consultation_1d
    partition (dt)
select doctor_id,
       name doctor_name,
       consultation_count,
       '$date'
from (select doctor_id,
             '$date',
             count(*) consultation_count
      from dwd_trade_consultation_inc
      group by doctor_id,
               '$date') avg
         left join (select id,
                           name
                    from dim_doctor_full
                    where dt = '$date') doc
                   on avg.doctor_id = doc.id;"
        execute_initial_load "$sql"
        ;;
    *)
        echo "不支持的表名: $table_name"
        exit 1
        ;;
esac

echo "首日加载完成"